package com.itheima.dubbo.mysql.mapper;

import com.itheima.commons.model.domain.LocalTotal;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;

public interface LocalTotalMapper {
    /**
     * 统计地区合计
     * @return
     */
    @Select("SELECT city as title,COUNT(*) as amount FROM \n" +
            "(select case  when city in ('河北省','上海市')then '华西地区' when city in('内蒙古自治区','山西省','辽宁省','吉林省','黑龙江省','江苏省','浙江省') then '华北地区' when city in('安徽省','福建省','江西省','山东省','河南省','湖北省','湖南省','广东省','广西省','海南省','四川省','贵州省','云南省','陕西省') then '华东地区' when city in('甘肃省','青海省','宁夏省','新疆维吾尔自治区','北京市','天津市','重庆市','香港','澳门') then '华南地区'\n" +
            "end as city from tb_user_info where created>#{startTime} and  created<#{endTime} ) as a GROUP BY city")
    List<LocalTotal> query(String startTime, String endTime);
}
